package classes;

import java.awt.Image;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.sql.Blob;
import java.sql.Connection;
import java.sql.Date;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;
import javax.imageio.ImageIO;
import javax.swing.JOptionPane;
import org.json.JSONArray;
import org.json.JSONException;
import org.json.JSONObject;

public class DB {

    private static String user = "root";
    private static String pass = "p@ssword";
    private static String host = "localhost";
    private static String port = "3306";
    private static String url = "jdbc:mysql://" + host + ":" + port + "/lifebook";

    public static Connection connect() throws ClassNotFoundException, SQLException {
        Connection conn = null;
        Class.forName("com.mysql.jdbc.Driver");
        conn = DriverManager.getConnection(url, user, pass);
        return conn;
    }

    public static LifebookUser login(String username, String password) throws Exception {
        Connection c = connect();
        PreparedStatement ps = c.prepareStatement("select * from users where username = ?");
        ps.setString(1, username);
        ResultSet rs = ps.executeQuery();
        if (rs.next()) {
            if (rs.getString("password").equals(password)) {
                LifebookUser lu = new LifebookUser(
                        rs.getString("username"),
                        rs.getString("email"),
                        rs.getString("password"),
                        rs.getString("firstName"),
                        rs.getString("lastName"),
                        rs.getString("sex").charAt(0),
                        rs.getDate("birthDay"),
                        rs.getString("aboutMe"),
                        rs.getBoolean("isEnabled"));
                c.close();
                if (lu.isIsEnabled() == false) {
                    throw new Exception("The user is disabled");
                }
                return lu;
            } else {
                c.close();
                throw new Exception("The password is invalid");
            }
        } else {
            c.close();
            throw new Exception("The user does not exist");
        }
    }

    public static void registerAccount(String username, String password, String email, String firstName,
            String lastName, char sex, String birthDay) throws ClassNotFoundException, SQLException {
        Connection c = connect();
        PreparedStatement ps = c.prepareStatement("insert into users (username, email, password, firstName, lastName, sex, birthDay, aboutMe, isEnabled) values (?,?,?,?,?,?,?,'',true)");
        ps.setString(1, username);
        ps.setString(2, email);
        ps.setString(3, password);
        ps.setString(4, firstName);
        ps.setString(5, lastName);
        ps.setString(6, Character.toString(sex));
        ps.setString(7, birthDay);
        ps.executeUpdate();
        c.close();
    }

    public static void uploadImage(String username, File imageFile) throws ClassNotFoundException, SQLException, FileNotFoundException, IOException {
        FileInputStream imageInputStream = new FileInputStream(imageFile);

        Image tempImage = ImageIO.read(imageFile);

        Connection c = connect();
        PreparedStatement ps = c.prepareStatement("insert into image (ownerUsername, imageFile, viewCount,width, height) values (?,?,0,?,?)");
        ps.setString(1, username);
        ps.setBinaryStream(2, imageInputStream);
        ps.setInt(3, tempImage.getWidth(null));
        ps.setInt(4, tempImage.getHeight(null));
        ps.executeUpdate();
        c.close();
    }

    public static Blob getImageFromID(String imageID) throws SQLException, ClassNotFoundException {
        Connection c = connect();
        PreparedStatement ps = c.prepareStatement("select imageFile from image where imageID = ?");
        ps.setString(1, imageID);
        ResultSet rs = ps.executeQuery();

        if (rs.next()) {
            return rs.getBlob(1);
        }
        return null;
    }

    public static List<LifebookImage> getImages(LifebookImageSearchParameters searchParameters) throws SQLException, ClassNotFoundException {
        if (searchParameters == null) {
            searchParameters = new LifebookImageSearchParameters();
        }
        Connection c = connect();
        PreparedStatement ps = c.prepareStatement(searchParameters.getSearchString());
        ResultSet rs = ps.executeQuery();

        List<LifebookImage> imageIDs = new ArrayList<LifebookImage>();
        while (rs.next()) {
            imageIDs.add(new LifebookImage(
                    rs.getInt("imageID"),
                    rs.getString("ownerUsername"),
                    rs.getString("imageTitle"),
                    rs.getString("imageCaption"),
                    rs.getBoolean("isEnabled"),
                    rs.getInt("viewCount"),
                    rs.getDate("Date"),
                    rs.getInt("likes"),
                    rs.getInt("width"),
                    rs.getInt("height")));
        }
        c.close();
        return imageIDs;
    }

    public static int getTempImageUploadIDByUser(String ownerUsername) throws SQLException, ClassNotFoundException {
        Connection c = connect();
        PreparedStatement ps = c.prepareStatement("select imageID from image where isEnabled is NULL and ownerUsername = ? order by `Date` Desc");
        ps.setString(1, ownerUsername);
        ResultSet rs = ps.executeQuery();


        if (rs.next()) {
            int imageID = rs.getInt("imageID");
            cleanTempUploadsByUser(ownerUsername, imageID);
            c.close();
            return imageID;
        } else {
            c.close();
            return -1;
        }
    }

    public static void cleanTempUploadsByUser(String ownerUsername, int imageID) throws SQLException, ClassNotFoundException {
        Connection c = connect();
        PreparedStatement ps = c.prepareStatement("delete from image where ownerUsername = ? and isEnabled is NULL and imageID != ?");
        ps.setString(1, ownerUsername);
        ps.setInt(2, imageID);
        ps.executeUpdate();
        c.close();
    }

    public static void updateTempUploadedImage(int imageID, String imageTitle, String imageCaption) throws SQLException, ClassNotFoundException {
        Connection c = connect();
        PreparedStatement ps = c.prepareStatement(
                "update image\n"
                + "set imageTitle = ?, imageCaption = ?, isEnabled = 1\n"
                + "where imageID = ?");
        ps.setString(1, imageTitle);
        ps.setString(2, imageCaption);
        ps.setInt(3, imageID);
        ps.executeUpdate();
        c.close();
    }

    public static void likeImage(String username, int imageID) throws SQLException, ClassNotFoundException {
        Connection c = connect();
        PreparedStatement ps = null;
        if (imageIsLikedByUser(username, imageID)) {
            ps = c.prepareStatement("delete from likedimages where username = ? and imageID = ?");
        } else {
            ps = c.prepareStatement("insert into likedimages (username, imageID) values (?,?)");
        }
        ps.setString(1, username);
        ps.setInt(2, imageID);
        ps.executeUpdate();
        c.close();
    }

    public static int getImageLikes(int imageID) throws SQLException, ClassNotFoundException {
        Connection c = connect();
        PreparedStatement ps = c.prepareStatement(
                "select count(likedimages.imageID) as 'likes'"
                + " from image left join likedimages on likedimages.imageID = image.imageID"
                + " where image.imageID = ?");
        ps.setInt(1, imageID);
        ResultSet rs = ps.executeQuery();

        if (rs.next()) {
            return rs.getInt("likes");
        } else {
            return 0;
        }
    }

    public static boolean imageIsLikedByUser(String username, int imageID) throws SQLException, ClassNotFoundException {
        Connection c = connect();
        PreparedStatement ps = c.prepareStatement("select * from likedimages where username = ? and imageID = ?");
        ps.setString(1, username);
        ps.setInt(2, imageID);

        ResultSet rs = ps.executeQuery();
        return rs.next();
    }

    public static void editProfile(String email, String password, String firstName, String lastName, char sex, String birthDay, String aboutMe, String username) throws SQLException, ClassNotFoundException {
        Connection c = connect();
        PreparedStatement ps = c.prepareStatement("UPDATE users SET email = ?, password = ?, firstName = ?, lastName = ?, sex = ?, birthDay = ?, aboutMe = ? WHERE username = ?");
        ps.setString(1, email);
        ps.setString(2, password);
        ps.setString(3, firstName);
        ps.setString(4, lastName);
        ps.setString(5, Character.toString(sex));
        ps.setString(6, birthDay);
        ps.setString(7, aboutMe);
        ps.setString(8, username);
        ps.executeUpdate();
        c.close();
    }

    public static void deactivateAccount(String username) throws SQLException, ClassNotFoundException {
        Connection c = connect();
        PreparedStatement ps = c.prepareStatement("UPDATE users SET isEnabled = '0' WHERE username = ?");
        ps.setString(1, username);
        ps.executeUpdate();
        c.close();
    }

    public static Integer[] getImageDimensionsFromID(int imageID) throws SQLException, ClassNotFoundException {
        Connection c = connect();
        PreparedStatement ps = c.prepareStatement("select width, height from image where imageID = ?");
        ps.setInt(1, imageID);
        ResultSet rs = ps.executeQuery();
        if (rs.next()) {
            return new Integer[]{rs.getInt("width"), rs.getInt("height")};
        } else {
            return new Integer[]{256, 256};
        }
    }

    public static List<String> getCategories() throws SQLException, ClassNotFoundException {
        Connection c = connect();
        PreparedStatement ps = c.prepareStatement("select * from pincategories");
        ResultSet rs = ps.executeQuery();

        List<String> categoryList = new ArrayList<String>();

        while (rs.next()) {
            categoryList.add(rs.getString("pinCategory"));
        }

        c.close();
        return categoryList;
    }

    public static void addPins(int imageID, String pinData) throws SQLException, ClassNotFoundException, JSONException {
        JSONArray pins = new JSONArray(pinData);

        for (int i = 0; i < pins.length(); i++) {
            JSONObject pin = pins.getJSONObject(i);
            String pinInfo = pin.getString("pinInfo");
            String pinCategory = pin.getString("pinCategory");
            int posX = pin.getInt("posX");
            int posY = pin.getInt("posY");
            JSONArray pinTagsArray = new JSONArray(pin.getString("pinTagsArray"));

            Connection c = connect();
            PreparedStatement ps = c.prepareStatement("insert into imagepins (imageID, pinCategory, pinInfo, x, y)\n"
                    + "values (?,?,?,?,?)", Statement.RETURN_GENERATED_KEYS);
            ps.setInt(1, imageID);
            ps.setString(2, pinCategory);
            ps.setString(3, pinInfo);
            ps.setInt(4, posX);
            ps.setInt(5, posY);
            ps.executeUpdate();
            try {
                ResultSet rs = ps.getGeneratedKeys();
                rs.next();
                int pinID = rs.getInt(1);

                for (int j = 0; j < pinTagsArray.length(); j++) {
                    String pinTag = pinTagsArray.getString(j);
                    if (pinTag.trim().length() > 0) {
                        try {
                            PreparedStatement ps2 = c.prepareStatement("insert into pintag (pinID, pinTag) values (?,?)");
                            ps2.setInt(1, pinID);
                            ps2.setString(2, pinTag);
                            ps2.executeUpdate();
                        } catch (Exception e) {
                        }
                    }
                }
            } catch (Exception ex) {
            }
            c.close();
        }
    }

    public static List<LifebookImagePin> getGetImagePins(int imageID) throws SQLException, ClassNotFoundException {
        List<LifebookImagePin> imagePins = new ArrayList<LifebookImagePin>();
        Connection c = connect();
        PreparedStatement ps = c.prepareStatement("select * from imagePins where imagePins.imageID = ?");
        ps.setInt(1, imageID);
        ResultSet rs = ps.executeQuery();
        while (rs.next()) {
            //Needed Varables
            int pinID = rs.getInt("pinID");
            String pinCategory = rs.getString("pinCategory");
            String pinInfo = rs.getString("pinInfo");
            int x = rs.getInt("x");
            int y = rs.getInt("y");

            //List of tags per pin
            List<String> pinTags = new ArrayList<String>();
            PreparedStatement ps2 = c.prepareStatement("select pinTag from imagePins left join pintag on pintag.pinID = imagePins.pinID where imagePins.pinID = ?");
            ps2.setInt(1, pinID);
            ResultSet rs2 = ps2.executeQuery();
            while (rs2.next()) {
                pinTags.add(rs2.getString("pinTag"));
            }

            LifebookImagePin pin = new LifebookImagePin(pinID, imageID, pinCategory, pinInfo, x, y, pinTags);
            imagePins.add(pin);
        }

        c.close();
        return imagePins;
    }

    public static boolean pinIsLikedByUser(String username, int pinID) throws ClassNotFoundException, SQLException {
        Connection c = connect();
        PreparedStatement ps = c.prepareStatement("select * from likedpins where username = ? and pinID = ?");
        ps.setString(1, username);
        ps.setInt(2, pinID);
        ResultSet rs = ps.executeQuery();
        return (rs.next());
    }

    public static boolean likePin(String username, int pinID) throws ClassNotFoundException, SQLException {
        Connection c = connect();
        if (pinIsLikedByUser(username, pinID)) {
            PreparedStatement ps = c.prepareStatement("delete from likedpins where pinID = ? and username = ?");
            ps.setInt(1, pinID);
            ps.setString(2, username);
            ps.executeUpdate();
            c.close();

        } else {
            PreparedStatement ps = c.prepareStatement("insert into likedpins (pinID, username) values (?, ?)");
            ps.setInt(1, pinID);
            ps.setString(2, username);
            ps.executeUpdate();
            c.close();
        }
        return pinIsLikedByUser(username, pinID);
    }

    public static void deactivatePhoto(int imageID) throws ClassNotFoundException, SQLException {
        Connection c = connect();
        PreparedStatement ps = c.prepareStatement("update image set isEnabled = 0 where imageID = ?");
        ps.setInt(1, imageID);
        ps.executeUpdate();
        c.close();
    }

    public static Blob getUserImageFromUsername(String username) throws SQLException, ClassNotFoundException {
        Connection c = connect();
        PreparedStatement ps = c.prepareStatement("select profileImage from users where username = ?");
        ps.setString(1, username);
        ResultSet rs = ps.executeQuery();

        rs.next();
        if (rs.getBlob(1) != null) {
            return rs.getBlob(1);
        } else {
            return getUserImageFromUsername("lifebook");
        }
    }

    public static LifebookUser getUserFromUsername(String username) throws SQLException, ClassNotFoundException {
        Connection c = connect();
        PreparedStatement ps = c.prepareStatement("select * from users where username = ?");
        ps.setString(1, username);
        ResultSet rs = ps.executeQuery();

        if (rs.next()) {
            LifebookUser lu = new LifebookUser(
                    rs.getString("username") != null ? rs.getString("username") : "",
                    rs.getString("email") != null ? rs.getString("email") : "",
                    rs.getString("password") != null ? rs.getString("password") : "",
                    rs.getString("firstName") != null ? rs.getString("firstName") : "",
                    rs.getString("lastName") != null ? rs.getString("lastName") : "",
                    rs.getString("sex").charAt(0),
                    rs.getDate("birthDay") != null ? rs.getDate("birthDay") : new Date(0),
                    rs.getString("aboutMe") != null ? rs.getString("aboutMe") : "",
                    rs.getBoolean("isEnabled"));
            return lu;
        }
        return null;
    }

    public static void updateUserProfileImage(String username, File imageFile) throws SQLException, ClassNotFoundException, IOException {
        FileInputStream imageInputStream = new FileInputStream(imageFile);

        Connection c = connect();
        PreparedStatement ps = c.prepareStatement("UPDATE `lifebook`.`users` SET `profileImage`= ? WHERE `username`= ? ;");
        ps.setBinaryStream(1, imageInputStream);
        ps.setString(2, username);
        ps.executeUpdate();
        c.close();
    }

    public static boolean userIsLikedByCurrentUser(String user, String currentUser) throws SQLException, ClassNotFoundException {
        Connection c = connect();
        PreparedStatement ps = c.prepareStatement("select * from followers where username = ? and follower = ?");
        ps.setString(1, user);
        ps.setString(2, currentUser);

        ResultSet rs = ps.executeQuery();
        return rs.next();
    }

    public static boolean followUser(String user, String currentUser) throws SQLException, ClassNotFoundException {
        Connection c = connect();
        PreparedStatement ps = null;
        if (userIsLikedByCurrentUser(user, currentUser)) {
            ps = c.prepareStatement("DELETE FROM `lifebook`.`followers` WHERE `username`= ? and`follower`= ?;");
        } else {
            ps = c.prepareStatement("INSERT INTO `lifebook`.`followers` (`username`, `follower`) VALUES (?, ?);");
        }
        ps.setString(1, user);
        ps.setString(2, currentUser);
        ps.executeUpdate();
        c.close();
        return (userIsLikedByCurrentUser(user, currentUser));
    }

    public static int getFollowerCount(String username) throws SQLException, ClassNotFoundException {
        Connection c = connect();
        PreparedStatement ps = c.prepareStatement("select count(username) as followerCount from followers\n"
                + "where username = ?");
        ps.setString(1, username);
        ResultSet rs = ps.executeQuery();
        if (rs.next()) {
            return rs.getInt("followerCount");
        } else {
            return 0;
        }
    }

    public static int getFollowingCount(String username) throws SQLException, ClassNotFoundException {
        Connection c = connect();
        PreparedStatement ps = c.prepareStatement("select count(follower) as followingCount from followers\n"
                + "where follower = ?");
        ps.setString(1, username);
        ResultSet rs = ps.executeQuery();
        if (rs.next()) {
            return rs.getInt("followingCount");
        } else {
            return 0;
        }
    }

    public static List<LifebookUser> searchLifebookUsers(LifebookUserSearchParameters searchParameters) throws SQLException, ClassNotFoundException {
        Connection c = connect();
        PreparedStatement ps;
        ps = c.prepareStatement(searchParameters.getSearchString());

        ResultSet rs = ps.executeQuery();
        List<LifebookUser> userList = new ArrayList<LifebookUser>();
        while (rs.next()) {
            LifebookUser lu = new LifebookUser(
                    rs.getString("username"),
                    rs.getString("email"),
                    rs.getString("password"),
                    rs.getString("firstName"),
                    rs.getString("lastName"),
                    rs.getString("sex").charAt(0),
                    rs.getDate("birthDay"),
                    rs.getString("aboutMe"),
                    rs.getBoolean("isEnabled"));
            userList.add(lu);
        }
        return userList;
    }

    public static void updatePinInfo(int pinID, String pinInfo, String pinCategory, List<String> pinTagsList) throws SQLException, ClassNotFoundException {
        Connection c = connect();
        PreparedStatement ps;
        ps = c.prepareStatement("UPDATE `lifebook`.`imagepins` SET `pinCategory`= ?, `pinInfo`= ? WHERE `pinID`= ?;");
        ps.setString(1, pinCategory);
        ps.setString(2, pinInfo);
        ps.setInt(3, pinID);
        ps.executeUpdate();

        ps.clearParameters();
        ps = c.prepareStatement("delete from pinTag where pinID = ?");
        ps.setInt(1, pinID);
        ps.executeUpdate();

        for (String pinTag : pinTagsList) {
            try {
                ps.clearParameters();
                ps = c.prepareCall("INSERT INTO `lifebook`.`pintag` (`pinID`, `pinTag`) VALUES (?, ?);");
                ps.setInt(1, pinID);
                ps.setString(2, pinTag.trim());
                ps.executeUpdate();
            } catch (Exception ex) {
            }
        }

        c.close();
    }

    public static void addNewPin(int imageID, String pinCategory, String pinInfo, int x, int y, List<String> pinTags) throws SQLException, ClassNotFoundException {
        Connection c = connect();
        PreparedStatement ps = c.prepareStatement("insert into imagepins (imageID, pinCategory, pinInfo, x, y)\n"
                + "values (?,?,?,?,?)", Statement.RETURN_GENERATED_KEYS);
        ps.setInt(1, imageID);
        ps.setString(2, pinCategory);
        ps.setString(3, pinInfo);
        ps.setInt(4, x);
        ps.setInt(5, y);
        ps.executeUpdate();

        try {
            ResultSet rs = ps.getGeneratedKeys();
            rs.next();
            int pinID = rs.getInt(1);

            for (String tag : pinTags) {
                ps.clearParameters();
                ps = c.prepareStatement("insert into pintag (pinID, pinTag) values (?,?)");
                ps.setInt(1, pinID);
                ps.setString(2, tag.trim());
                ps.executeUpdate();
            }
        } catch (Exception ex) {
        } finally {
            c.close();
        }
    }

    public static void deletePin(int pinID) throws SQLException, ClassNotFoundException {
        Connection c = connect();
        PreparedStatement ps = c.prepareStatement("delete from pintag where pinID = ?;\n");
        ps.setInt(1, pinID);
        ps.executeUpdate();

        ps = c.prepareStatement("delete from likedpins where pinID = ?;\n");
        ps.setInt(1, pinID);
        ps.executeUpdate();

        ps = c.prepareStatement("delete from imagepins where pinID = ?;");
        ps.setInt(1, pinID);
        ps.executeUpdate();

        c.close();
    }

    public static int getPinLikes(int pinID) throws SQLException, ClassNotFoundException {
        Connection c = connect();
        PreparedStatement ps = c.prepareStatement("select count(username) as `pinlikes` from likedpins\n"
                + "where pinID = ?");
        ps.setInt(1, pinID);
        ResultSet rs = ps.executeQuery();

        if (rs.next()) {
            return rs.getInt("pinlikes");
        }

        c.close();
        return 0;
    }

    public static boolean userExists(String username) throws SQLException, ClassNotFoundException {
        Connection c = connect();
        PreparedStatement ps = c.prepareStatement("select * from users\n"
                + "where username = ?");
        ps.setString(1, username);
        
        ResultSet rs = ps.executeQuery();
        
        return rs.next();
    }
}
